from sqlalchemy import create_engine

# MySQL - Python
# mysql + mysqldb: // < user >: < password >@< host > [: < port >] / < dbname >
# pymysql
# mysql + pymysql: // < username >: < password >@< host > / < dbname > [? < options >]
# MySQL - Connector
# mysql + mysqlconnector: // < user >: < password >@< host > [: < port >] / < dbname >
# cx_Oracle
# oracle + cx_oracle: // user:pass@host:port / dbname[?key = value & key = value...]
# 更多详见：http: // docs.sqlalchemy.org / en / latest / dialects / index.html
# 以mysql为例：
# engine = create_engine("mysql+pymysql://root:123456@localhost:3306/db4?charset=utf8",
#     max_overflow=0,  # 超过连接池大小外最多创建的连接
#     pool_size=5,  # 连接池大小
#     pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错
#     pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
#     echo = True    # echo参数为True时，会显示每条执行的SQL语句，可以关闭 ",
#     max_overflow = 5)

engine = create_engine("mysql+pymysql://linrui:123456@127.0.0.1:3306/kaifamiao?charset=utf8mb4",echo=False,max_overflow=5)

from sqlalchemy import create_engine
import tushare as ts
# 设置token
ts.set_token('531f7eda35d265a43c7091a5971802aab320a80c16afb6e7164c595a')
# 初始化pro接口
pro = ts.pro_api()
df = pro.stock_basic(exchange='', list_status='L', fields='ts_code,symbol,name,area,industry,list_date')
#print(df)
#存入数据库
conn = engine.connect()  # 连接
sql="select * from information_schema.TABLES where TABLE_NAME = 'stocklist'"
result = conn.execute(sql)
print(result.rowcount)
if(result.rowcount ==0 ):
    print('数据表不存在，将新建表')
    df.to_sql('stocklist', engine)

if (result.rowcount == 1):
    print('数据表存在，先删除原有数据表')
    conn.execute('drop table stocklist')  # 执行SQL
    df.to_sql('stocklist', engine)
##try:
# conn.execute('drop table stocklist')  # 执行SQL
##except OSError as err:
##print("OS error: {0}".format(err))
##print('数据表不存在')
#
# 追加数据到现有表
# df.to_sql('tick_data',engine,if_exists='append')

conn = engine.connect()  # 连接
try:
    # 执行SQL语句
    results = conn.execute("select *from stocklist")
    # 获取所有记录列表
    results = conn.execute("select *from stocklist")
    for row in results:
        fname = row[0]
        lname = row[1]
        age = row[2]
        sex = row[3]
        income = row[4]
        # 打印结果
        print("ID=%s,CODE=%s,Market=%s,Name=%s,地方=%s" % \
              (fname, lname, age, sex, income))
except:
    print("Error: unable to fetch data")

conn.close()  # 关闭连接
